
package DataBase;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author admin
 */
public class DBQuery {

    /**
     *
     * @param conn
     * @param loginname
     * @param password
     * @return wert
     * @throws SQLException
     * Logindaten zur Verbindungsherstellung
     */
    public static boolean ValidateCredentials(Connection conn, String loginname, String password) throws SQLException {
        Statement sqlCmd = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet result = sqlCmd.executeQuery("SELECT count(*) as Count from users where loginname = '"
                + loginname
                + "' AND passwort = '"
                + password + "'");
        int rowCount = 0;
        conn.close();
        // Ausgabe des Resultats der Select * Abfrage
        // Muss für jede Spalte einzeln ausgegeben werden
        while (result.next()) {
            rowCount = Integer.parseInt(result.getString("Count"));
        }
        if (rowCount > 0) {
            return true;
        }
        return false;
    }
    
    /**
     *
     * @param conn
     * @param value
     * @param sensor
     * @return
     * Füllt die Berechnete Temperatur ab
     */
    public static boolean InsertTemperture(Connection conn, String value,int sensor)
    {
        boolean result = false;
        if(value.length() >= 4)
        {
            try {
               
                Statement sqlCmd = conn.createStatement();
                result = sqlCmd.execute("INSERT INTO STAMP (wert,tmdate,sensor) VALUES(" 
                        + value
                        +",CURRENT_TIMESTAMP,"
                        + sensor
                        +")");
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }
    /**
     *
     * @param conn
     * @param sensor
     * @return wert
     * @throws SQLException
     * Holt Parameter von DB
     */
    public static int GetNewestTemperture(Connection conn,int sensor) throws SQLException 
    {
        Statement sqlCmd = conn.createStatement();
        ResultSet result = sqlCmd.executeQuery("SELECT * FROM STAMP ORDER BY SKEY DESC LIMIT 1");
        conn.close();
        while (result.next()) {
            return Integer.parseInt(result.getString("WERT"));
        }
        return 0;
    }
    /**
     *
     * @param conn
     * @return
     * Bestimmt den Ort
     */
    public static ArrayList<String> GetAvailableLocations(Connection conn)
    {
        ArrayList<String> locations = new ArrayList<>();
        try {
            Statement sqlCmd = conn.createStatement();
            ResultSet result = sqlCmd.executeQuery("SELECT LOCATION FROM ORT");
            

            while (result.next()) {
                locations.add(result.getString("location"));
            }
            conn.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return locations;
    }
    
    /**
     *
     * @param conn
     * @return
     * Holt zu berechnende Paramater
     */
    public static ArrayList<String> GetParameters(Connection conn)
    {
        ArrayList<String> params = new ArrayList<>();
        try {
            Statement sqlCmd = conn.createStatement();
            ResultSet result = sqlCmd.executeQuery("SELECT * FROM MATH ORDER BY MKEY");
           
            while (result.next()) {
                params.add(result.getString("wert"));
                params.add(result.getString("temp"));
            }
            conn.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return params;
    }
    
    /**
     *
     * @param conn
     * @param param1
     * @param p1Temp
     * @param param2
     * @param p2Temp
     * @param param3
     * @param p3Temp
     * @param param4
     * @param p4Temp
     * @return
     * Demperaturparameter für Rechnung
     */
    public static boolean InsertUpdateParameter(Connection conn, String param1,String p1Temp, String param2,String p2Temp, String param3,String p3Temp,String param4,String p4Temp)
    {
        boolean result = false;
        try {
           Statement sqlCmd = conn.createStatement();
           result = sqlCmd.execute("UPDATE MATH SET WERT = '" + param1 + "' WHERE MKEY = 1");
           result = sqlCmd.execute("UPDATE MATH SET WERT = '" + param2 + "' WHERE MKEY = 2");
           result = sqlCmd.execute("UPDATE MATH SET WERT = '" + param3 + "' WHERE MKEY = 3");
           conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
    /**
     *
     * @param conn
     * @param dateFrom
     * @param dateTill
     * @param path
     * @return
     * Holt Daten
     */
    
    public static int GetDataFromRange(Connection conn, java.util.Date dateFrom,java.util.Date dateTill,String path)
    {
        boolean result = false;
        try {
            java.sql.Timestamp from = new Timestamp(dateFrom.getTime());  
            java.sql.Timestamp till = new Timestamp(dateTill.getTime());

            Statement sqlCmd = conn.createStatement();                        
            result = sqlCmd.execute("copy (select wert,';' as seperator1,tmdate, ';' as seperator2,sensor from stamp where tmdate between '" 
                    + from + "' and '" + till +"') To '" + path + "'");
            conn.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
        return 1;
    }
    
}
